import json
import time

from flask import Flask, request, jsonify
import numpy as np
import onnxruntime
import datetime
import pymysql
from train import StartTrain

# app = Flask(__name__)

# 加载ONNX模型
# sess = onnxruntime.InferenceSession("../checkpoint/pytorch/lstm_model.onnx")

# 预测数据请求
# @app.route('/predict', methods=['POST'])
def predict():
    start = time.time()
    sess = onnxruntime.InferenceSession("../checkpoint/pytorch/lstm_model.onnx")
    # data = request.json  # 接收输入数据，假设数据格式为(168,9)的数组
    # array = eval(data.get("data"))
    # input_data = np.array(array).astype('float32')  # 将数据转换为numpy数组，并转换为float32类型

    DB,conDB = connectDB()
    # 查询最新的168*9的数据
    DB.execute("SELECT WateId,Level FROM `md_wate_meas` ORDER BY `MeasDT` DESC LIMIT 1512;")
    DB_data = DB.fetchall()
    DB.execute("SELECT MeasDT FROM `md_wate_meas` ORDER BY `MeasDT` DESC LIMIT 1;")
    date = DB.fetchone()
    input_data = []
    if DB_data[len(DB_data) - 1][0] != DB_data[len(DB_data) - 10][0]:
        raise Exception("时间周期错误！")

    Row = []
    # 整理数据至二维
    for index in range(int(len(DB_data))):
        if index % 9 == 0 and index != 0:
            input_data.append(Row)
            Row = []
        Row.append(DB_data[index][1])
        if index == int(len(DB_data)-1):
            input_data.append(Row)

    input_data = np.array(input_data,dtype="float32")

    input_data = CleanData(input_data)

    # 从 JSON 文件中读取平均数和方差
    with open("../checkpoint/pytorch/config.json", "r") as f:
        config = json.load(f)
    mean = np.array(config["mean"])
    std = np.array(config["std"])

    input_data = (input_data - mean) / std

    input_names = ["input"]
    output_names = ["output"]
    x_input = np.array(input_data, dtype=np.float32).reshape((1, 168, 9))
    ort_inputs = {input_names[0]: x_input}
    ort_outs = sess.run(output_names, ort_inputs)

    prediction = ort_outs[0].reshape(-1)  # 将输出reshape为(72,1)数据
    prediction = prediction * std[8] + mean[8]

    try:
        # 开始事务
        conDB.begin()

        # 删除预测表
        sql1 = "DELETE FROM md_wate_predict;"
        DB.execute(sql1)

        # 每天预测4次,隔8小时一次
        for index in range(int(prediction.shape[0] / 8)):
            PredDT = date[0] + datetime.timedelta(hours=8 * (index + 1))
            Value = prediction[index * 8:index * 8 + 8].mean()
            Value = round(Value, 2)
            #
            sql = "INSERT INTO md_wate_predict_hist (WateId,Kind,PredDT,Value) VALUES (%s,%s,%s,%s)"
            DB.execute(sql, ('11', 1, PredDT, Value))
            sql = "INSERT INTO md_wate_predict (WateId,Kind,PredDT,Value) VALUES (%s,%s,%s,%s)"
            DB.execute(sql, ('11', 1, PredDT, Value))

        # 提交事务
        conDB.commit()
        print("事务提交成功！")

    except Exception as e:
        # 回滚事务
        conDB.rollback()
        print("事务回滚，原因：", str(e))

    # 关闭连接对象，否则会导致连接泄漏，消耗数据库资源
    DB.close()
    # 关闭光标
    DB.close()

    return jsonify("预测数据已更新")

# 接收训练数据请求
# @app.route('/train', methods=['POST'])
def trainONNX():
    start = time.time()
    DB = connectDB()
    StartTrain(DB)

    # 关闭连接对象，否则会导致连接泄漏，消耗数据库资源
    DB.close()
    # 关闭光标
    DB.close()

    trainTime = time.time() - start
    Output = "训练完成，耗时" + str("{:.2f}".format(trainTime)) + "S"
    print(Output)
    return jsonify(200)

def connectDB():
    # 连接数据库
    connection = pymysql.connect(host='58.48.78.146', # host属性
    port=3006,  # 端口，默认为3306
    user = 'root', # 用户名
    password = 'WhGhj@hd3q8!', # 此处填登录数据库的密码
    db = 'lydw2020_wh', # 数据库名
    charset = 'utf8',  # 字符编码
    )
    cur = connection.cursor()

    return cur,connection

# 找出异常数据并替换
def CleanData(data):
    # 循环遍历每列数据
    for i in range(data.shape[1]):
        col = data[:, i]
        mean = col.mean()
        std = col.std()
        # 误差为10倍方差
        threshold = 10 * std

        # 根据均值和标准差计算异常值范围
        upper_bound = mean + threshold
        lower_bound = mean - threshold

        # 找到并打印异常值
        for row in range(data.shape[0]):
            if data[row,i] > upper_bound or data[row,i] < lower_bound:
                while(row != 0 or row != data.shape[0]):
                    if data[row-1,i] < upper_bound or data[row-1,i] > lower_bound:
                        print(f"Replace  row {row} col{i}")
                        data[row, i] = data[row-1, i]
                        break
                # print(f"row {row} col{i}")

    data = data[:,::-1]
    return data

if __name__ == '__main__':
    # app.run(host='127.0.0.1',port=5000,debug=True)
    trainONNX()
    # predict()